﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer;
using System.Windows.Forms;
using Microsoft.SqlServer.Management.UI.VSIntegration;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using System.Data;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Sdk.Sfc;
using System.Collections.Specialized;

namespace SSMSAddinDenali
{
    /// <summary>
    /// table menu item extension
    /// </summary>
    class tableMenuItem : ToolsMenuItemBase, IWinformsMenuHandler
    {
        /// <summary>
        /// Default constructor
        /// </summary>
        public tableMenuItem()
        {
            this.Text = "Script Data as INSERT";
        }

        /// <summary>
        /// Invoke
        /// </summary>
        protected override void Invoke()
        {
            
        }
        
        /// <summary>
        /// Clone
        /// </summary>
        /// <returns></returns>
        public override object Clone()
        {
            return new tableMenuItem();
        }

        #region IWinformsMenuHandler Members

        /// <summary>
        /// Get Menu Items
        /// </summary>
        /// <returns></returns>
        public System.Windows.Forms.ToolStripItem[] GetMenuItems()
        {
            ToolStripMenuItem item = new ToolStripMenuItem("Script Data as");
            
            ToolStripMenuItem insertItem = new ToolStripMenuItem("INSERT");
            insertItem.Tag = false;
            insertItem.Click += new EventHandler(InsertItem_Click);

            item.DropDownItems.Add(insertItem);


            ToolStripMenuItem scriptIt = new ToolStripMenuItem("Script Full table Schema");
            scriptIt.Click += new EventHandler(scriptIt_Click);

            return new ToolStripItem[] { item, scriptIt };
        }

        void scriptIt_Click(object sender, EventArgs e)
        {
            //throw new NotImplementedException();
                       Regex tableRegex = new Regex(@"^Server\[[^\]]*\]/Database\[@Name='(?<Database>[^']*)'\]/Table\[@Name='(?<Table>[^']*)' and @Schema='(?<Schema>[^']*)'\]$");
            Match match = tableRegex.Match(this.Parent.Context);
            if (match != null)
            {
                string tableName = match.Groups["Table"].Value;
                string schema = match.Groups["Schema"].Value;
                string database = match.Groups["Database"].Value;

                string currenServerName = this.Parent.Connection.ServerName;

                StringBuilder output = SMOGenerateSQL(currenServerName, database,tableName, schema);

                DTEApplicationHelper.CreateNewScriptWindow(output);
            }

        }

        private static StringBuilder SMOGenerateSQL(string currenServerName, string dbName, string tableName, string tableSchema)
        {
            Server server = new Server(currenServerName);
            Database db = server.Databases[dbName];
            List<Urn> list = new List<Urn>();

            list.Add(db.Tables[tableName, tableSchema].Urn);

            foreach (Index index in db.Tables[tableName, tableSchema].Indexes)
            {
                list.Add(index.Urn); 
            }

            foreach (ForeignKey foreignKey in db.Tables[tableName, tableSchema].ForeignKeys)
            {
                list.Add(foreignKey.Urn);
            }

            foreach (Trigger triggers in db.Tables[tableName, tableSchema].Triggers)
            {
                list.Add(triggers.Urn);
            }

            Scripter scripter = new Scripter();
            scripter.Server = server;
            scripter.Options.IncludeHeaders = true;
            scripter.Options.SchemaQualify = true;
            scripter.Options.SchemaQualifyForeignKeysReferences = true;
            scripter.Options.NoCollation = true;
            scripter.Options.DriAllConstraints = true;
            scripter.Options.DriAll = true;
            scripter.Options.DriAllKeys = true;
            scripter.Options.DriIndexes = true;
            scripter.Options.ClusteredIndexes = true;
            scripter.Options.NonClusteredIndexes = true;
            scripter.Options.ToFileOnly = false;
            //              scripter.Options.FileName = @"C:\tables.sql";
            StringCollection scriptedSQL =  scripter.Script(list.ToArray());

            StringBuilder sb = new StringBuilder();

            foreach (string s in scriptedSQL)
            {
                sb.AppendLine(s);
            }

            return sb;
            
        }

        #endregion

        /// <summary>
        /// Click on Insert 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        void InsertItem_Click(object sender, EventArgs e)
        {
            ToolStripMenuItem item = (ToolStripMenuItem)sender;
            bool generateColumnNames = (bool)item.Tag;

            Regex tableRegex = new Regex(@"^Server\[[^\]]*\]/Database\[@Name='(?<Database>[^']*)'\]/Table\[@Name='(?<Table>[^']*)' and @Schema='(?<Schema>[^']*)'\]$");
            Match match = tableRegex.Match(this.Parent.Context);
            if (match != null)
            {
                string tableName = match.Groups["Table"].Value;
                string schema = match.Groups["Schema"].Value;
                string database = match.Groups["Database"].Value;

                string connectionString =  this.Parent.Connection.ConnectionString  + ";Database=" + database;
                
                SqlCommand command = new SqlCommand(string.Format("SELECT * FROM [{0}].[{1}]", schema, tableName));
                command.Connection = new SqlConnection(connectionString);
                command.Connection.Open();

                SqlDataAdapter adapter = new SqlDataAdapter(command);
                DataTable table = new DataTable();
                adapter.Fill(table);

                command.Connection.Close();

                StringBuilder buffer = new StringBuilder();
                
                // generate INSERT prefix
                StringBuilder prefix = new StringBuilder();
                if (generateColumnNames)
                {
                    prefix.AppendFormat("INSERT INTO {0} (", tableName);
                    for (int i = 0; i < table.Columns.Count; i++)
                    {
                        if (i > 0) prefix.Append(", ");
                        prefix.AppendFormat("[{0}]", table.Columns[i].ColumnName);
                    }
                    prefix.Append(") VALUES (");
                }
                else
                    prefix.AppendFormat("INSERT INTO {0} VALUES (", tableName);

                // generate INSERT statements
                foreach (DataRow row in table.Rows)
                {
                    StringBuilder values = new StringBuilder();
                    for (int i = 0; i < table.Columns.Count; i++)
                    {
                        if (i > 0) values.Append(", ");

                        if (row.IsNull(i)) values.Append("NULL");
                        else if (table.Columns[i].DataType == typeof(int) ||
                            table.Columns[i].DataType == typeof(decimal) ||
                            table.Columns[i].DataType == typeof(long) ||
                            table.Columns[i].DataType == typeof(double) ||
                            table.Columns[i].DataType == typeof(float) ||
                            table.Columns[i].DataType == typeof(byte))
                            values.Append(row[i].ToString());
                        else
                            values.AppendFormat("'{0}'", row[i].ToString());
                    }
                    values.AppendFormat(")");

                    buffer.AppendLine(prefix.ToString() + values.ToString());
                }

                // create new document
                DTEApplicationHelper.CreateNewScriptWindow(buffer);
            }
        }
    }
}
